#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库模型 - 两张表设计
"""

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

class ClassSchedule(db.Model):
    """班级课表模型"""
    __tablename__ = 'class_schedules'
    
    id = db.Column(db.Integer, primary_key=True)
    
    # 班级信息
    class_id = db.Column(db.String(50), nullable=False, index=True)
    class_name = db.Column(db.String(200), nullable=False)
    
    # 课程信息
    course_code = db.Column(db.String(50))
    course_name = db.Column(db.String(200))
    teacher_name = db.Column(db.String(100))
    
    # 时间地点信息
    classroom = db.Column(db.String(100))
    weekday = db.Column(db.Integer)  # 1-7 表示周一到周日
    start_period = db.Column(db.Integer)
    end_period = db.Column(db.Integer)
    weeks = db.Column(db.String(100))  # 如：1-16周
    
    # 学期信息
    semester = db.Column(db.String(50), nullable=False, index=True)
    
    # 原始数据
    raw_data = db.Column(db.JSON)
    
    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.now)
    
    def __repr__(self):
        return f'<ClassSchedule {self.class_name} - {self.course_name}>'
    
    def to_dict(self):
        """转换为字典"""
        return {
            'id': self.id,
            'class_id': self.class_id,
            'class_name': self.class_name,
            'course_code': self.course_code,
            'course_name': self.course_name,
            'teacher_name': self.teacher_name,
            'classroom': self.classroom,
            'weekday': self.weekday,
            'start_period': self.start_period,
            'end_period': self.end_period,
            'weeks': self.weeks,
            'semester': self.semester,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class TeacherSchedule(db.Model):
    """教师课表模型"""
    __tablename__ = 'teacher_schedules'
    
    id = db.Column(db.Integer, primary_key=True)
    
    # 教师信息
    teacher_id = db.Column(db.String(50), nullable=False, index=True)
    teacher_name = db.Column(db.String(100), nullable=False)
    department = db.Column(db.String(200))
    teacher_title = db.Column(db.String(100))
    
    # 课程信息
    course_code = db.Column(db.String(50))
    course_name = db.Column(db.String(200))
    class_names = db.Column(db.Text)  # 上课班级，可能多个，用逗号分隔
    
    # 时间地点信息
    classroom = db.Column(db.String(100))
    weekday = db.Column(db.Integer)  # 1-7 表示周一到周日
    start_period = db.Column(db.Integer)
    end_period = db.Column(db.Integer)
    weeks = db.Column(db.String(100))  # 如：1-16周
    
    # 学期信息
    semester = db.Column(db.String(50), nullable=False, index=True)
    
    # 原始数据
    raw_data = db.Column(db.JSON)
    
    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.now)
    
    def __repr__(self):
        return f'<TeacherSchedule {self.teacher_name} - {self.course_name}>'
    
    def to_dict(self):
        """转换为字典"""
        return {
            'id': self.id,
            'teacher_id': self.teacher_id,
            'teacher_name': self.teacher_name,
            'department': self.department,
            'teacher_title': self.teacher_title,
            'course_code': self.course_code,
            'course_name': self.course_name,
            'class_names': self.class_names,
            'classroom': self.classroom,
            'weekday': self.weekday,
            'start_period': self.start_period,
            'end_period': self.end_period,
            'weeks': self.weeks,
            'semester': self.semester,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

# 数据库操作辅助类
class ScheduleManager:
    """课表数据管理器"""
    
    @staticmethod
    def get_class_list(semester=None):
        """获取班级列表"""
        query = db.session.query(
            ClassSchedule.class_id,
            ClassSchedule.class_name,
            ClassSchedule.semester,
            db.func.count(ClassSchedule.id).label('course_count')
        ).group_by(
            ClassSchedule.class_id,
            ClassSchedule.class_name,
            ClassSchedule.semester
        )
        
        if semester:
            query = query.filter(ClassSchedule.semester == semester)
            
        return query.all()
    
    @staticmethod
    def get_teacher_list(semester=None):
        """获取教师列表"""
        query = db.session.query(
            TeacherSchedule.teacher_id,
            TeacherSchedule.teacher_name,
            TeacherSchedule.department,
            TeacherSchedule.teacher_title,
            db.func.count(TeacherSchedule.id).label('course_count')
        ).group_by(
            TeacherSchedule.teacher_id,
            TeacherSchedule.teacher_name,
            TeacherSchedule.department,
            TeacherSchedule.teacher_title
        )
        
        if semester:
            query = query.filter(TeacherSchedule.semester == semester)
            
        return query.all()
    
    @staticmethod
    def get_class_schedule(class_id, semester=None):
        """获取班级课表"""
        query = ClassSchedule.query.filter_by(class_id=class_id)
        if semester:
            query = query.filter_by(semester=semester)
        return query.order_by(ClassSchedule.weekday, ClassSchedule.start_period).all()
    
    @staticmethod
    def get_teacher_schedule(teacher_id, semester=None):
        """获取教师课表"""
        query = TeacherSchedule.query.filter_by(teacher_id=teacher_id)
        if semester:
            query = query.filter_by(semester=semester)
        return query.order_by(TeacherSchedule.weekday, TeacherSchedule.start_period).all()
    
    @staticmethod
    def save_class_data(class_data_list):
        """保存班级课表数据"""
        count = 0
        for class_data in class_data_list:
            class_id = class_data.get('class_id')
            class_name = class_data.get('class_name')
            semester = class_data.get('semester', '2025-2026-1')
            
            if not class_id or not class_name:
                continue
            
            # 删除已存在的数据（避免重复）
            ClassSchedule.query.filter_by(class_id=class_id, semester=semester).delete()
            
            for schedule_data in class_data.get('schedules', []):
                schedule = ClassSchedule(
                    class_id=class_id,
                    class_name=class_name,
                    course_code=schedule_data.get('KCH'),
                    course_name=schedule_data.get('KCM'),
                    teacher_name=schedule_data.get('SKJS'),
                    classroom=schedule_data.get('JASMC'),
                    weekday=int(schedule_data.get('SKXQ', 1)),
                    start_period=int(schedule_data.get('KSJC', 1)),
                    end_period=int(schedule_data.get('JSJC', 1)),
                    weeks=schedule_data.get('ZCMC'),
                    semester=semester,
                    raw_data=schedule_data
                )
                db.session.add(schedule)
                count += 1
        
        db.session.commit()
        return count
    
    @staticmethod
    def save_teacher_data(teacher_data_list):
        """保存教师课表数据"""
        count = 0
        for teacher_data in teacher_data_list:
            teacher_id = teacher_data.get('teacher_id')
            teacher_name = teacher_data.get('teacher_name')
            semester = teacher_data.get('semester', '2025-2026-1')
            department = teacher_data.get('department')
            teacher_title = teacher_data.get('teacher_title')
            
            if not teacher_id or not teacher_name:
                continue
            
            # 删除已存在的数据（避免重复）
            TeacherSchedule.query.filter_by(teacher_id=teacher_id, semester=semester).delete()
            
            for schedule_data in teacher_data.get('schedules', []):
                schedule = TeacherSchedule(
                    teacher_id=teacher_id,
                    teacher_name=teacher_name,
                    department=department,
                    teacher_title=teacher_title,
                    course_code=schedule_data.get('KCH'),
                    course_name=schedule_data.get('KCM'),
                    class_names=schedule_data.get('SKBJ'),  # 上课班级
                    classroom=schedule_data.get('JASMC'),
                    weekday=int(schedule_data.get('SKXQ', 1)),
                    start_period=int(schedule_data.get('KSJC', 1)),
                    end_period=int(schedule_data.get('JSJC', 1)),
                    weeks=schedule_data.get('ZCMC'),
                    semester=semester,
                    raw_data=schedule_data
                )
                db.session.add(schedule)
                count += 1
        
        db.session.commit()
        return count

    @staticmethod
    def get_class_list_paginated(page=1, per_page=20, search='', year='', semester='', college=''):
        """获取班级列表（分页和筛选）"""
        # 基础查询
        query = db.session.query(
            ClassSchedule.class_id,
            ClassSchedule.class_name,
            ClassSchedule.semester,
            db.func.count(ClassSchedule.id).label('course_count')
        ).group_by(
            ClassSchedule.class_id,
            ClassSchedule.class_name,
            ClassSchedule.semester
        )

        # 搜索筛选
        if search:
            query = query.filter(ClassSchedule.class_name.contains(search))

        # 年级筛选（从班级名称中提取）
        if year:
            query = query.filter(ClassSchedule.class_name.contains(f'{year}级'))

        # 学期筛选
        if semester:
            query = query.filter(ClassSchedule.semester == semester)

        # 学院筛选（从班级名称中匹配）
        if college:
            # 这里可以根据实际情况调整学院名称匹配逻辑
            college_map = {
                '4014': '信息学院',
                '4015': '数学与数字经济学院',
                # 可以添加更多学院映射
            }
            college_name = college_map.get(college, college)
            query = query.filter(ClassSchedule.class_name.contains(college_name))

        # 排序
        query = query.order_by(ClassSchedule.class_name)

        # 分页
        total = query.count()
        items = query.offset((page - 1) * per_page).limit(per_page).all()

        # 计算分页信息
        pages = (total + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < pages

        # 格式化数据
        data = []
        for item in items:
            data.append({
                'class_id': item.class_id,
                'class_name': item.class_name,
                'semester': item.semester,
                'schedule_count': item.course_count
            })

        return {
            'data': data,
            'total': total,
            'pages': pages,
            'has_prev': has_prev,
            'has_next': has_next
        }

    @staticmethod
    def get_teacher_list_paginated(page=1, per_page=20, search='', department='', semester=''):
        """获取教师列表（分页和筛选）"""
        # 基础查询
        query = db.session.query(
            TeacherSchedule.teacher_id,
            TeacherSchedule.teacher_name,
            TeacherSchedule.department,
            TeacherSchedule.teacher_title,
            db.func.count(TeacherSchedule.id).label('course_count')
        ).group_by(
            TeacherSchedule.teacher_id,
            TeacherSchedule.teacher_name,
            TeacherSchedule.department,
            TeacherSchedule.teacher_title
        )

        # 搜索筛选（教师姓名或工号）
        if search:
            query = query.filter(
                db.or_(
                    TeacherSchedule.teacher_name.contains(search),
                    TeacherSchedule.teacher_id.contains(search)
                )
            )

        # 部门筛选
        if department:
            query = query.filter(TeacherSchedule.department.contains(department))

        # 学期筛选
        if semester:
            query = query.filter(TeacherSchedule.semester == semester)

        # 排序
        query = query.order_by(TeacherSchedule.teacher_name)

        # 分页
        total = query.count()
        items = query.offset((page - 1) * per_page).limit(per_page).all()

        # 计算分页信息
        pages = (total + per_page - 1) // per_page
        has_prev = page > 1
        has_next = page < pages

        # 格式化数据
        data = []
        for item in items:
            data.append({
                'teacher_id': item.teacher_id,
                'teacher_name': item.teacher_name,
                'department': item.department or '未知部门',
                'teacher_title': item.teacher_title or '教师',
                'schedule_count': item.course_count
            })

        return {
            'data': data,
            'total': total,
            'pages': pages,
            'has_prev': has_prev,
            'has_next': has_next
        }
